﻿
using Castle.Core.Internal;
using Coldairarrow.Business.Interface;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.Interface;
using Coldairarrow.Util;
using Newtonsoft.Json.Linq;
using OfficeOpenXml.Drawing.Slicer.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Tracing;
using System.Drawing;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Security.Cryptography;
using System.Threading;
using System.Threading.Tasks;
using static AutoMapper.Internal.CollectionMapperExpressionFactory;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace Coldairarrow.Business.Interface
{
    public class OL_Rep_InSalesStockInterfaceBusiness : Interface_BaseBusiness, IJob_Business, ITransientDependency
    {
        public List<OL_FilterDTO> Filters;
        public OL_Rep_InSalesStockInterfaceBusiness()
        {
            this.InterfaceName = "OL电商进销存明细";
            //获取字段的映射关系
            tableField = GetMapField("editRow");
            //索引字段
            Keys = GetKeys("editRow");
            indexFields.Add(Keys.Where(X => X.Type == "K").First().Field);
            //查询过滤条件
            Filters = new List<OL_FilterDTO>();


            //查询执行前的事件
            BeforeQueryEvent += InterfaceBusiness_BeforeQueryEvent;
            //查询执行中的事件
            OnQueryEvent += InterfaceBusiness_OnQueryEvent;
            //执行非查询请求中的事件
            OnNoQueryEvent += InterfaceBusiness_OnNoQueryEvent;

        }

        /// <summary>
        /// 执行非查询请求中时间
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="noQueryEventArgs"></param>
        private void InterfaceBusiness_OnNoQueryEvent(object sender, NoQueryEventArgs noQueryEventArgs)
        {
            //API请求是否成功
            noQueryEventArgs.Success = noQueryEventArgs.Result["success"].ToString().ToBool();
        }

        /// <summary>
        /// 查询执行前的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_BeforeQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //执行查询前添加过滤条件
            if (queryEventArgs.Page.Filters.IsNullOrEmpty() && !Filters.IsNullOrEmpty())
            {
                queryEventArgs.Page.Filters = Filters;
            }

        }

        /// <summary>
        /// 查询执行中的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="queryEventArgs"></param>
        private void InterfaceBusiness_OnQueryEvent(object sender, QueryEventArgs queryEventArgs)
        {
            //是否查询完成
            if (queryEventArgs.Result["data"]["rows"].ToList().Count == 0)
            {
                queryEventArgs.Page.Complete = true;
            }
            //向总行中添加新的行
            queryEventArgs.Rows.AddRange(queryEventArgs.Result["data"]["rows"].ToList());
            //获取总页数
            queryEventArgs.Page.Total = queryEventArgs.Result["data"]["total"].ToString().ToInt();
            //获取查询成功标志
            queryEventArgs.Success = queryEventArgs.Result["success"].ToString().ToBool();
        }


        /// <summary>
        /// 执行增删改查业务
        /// </summary>
        public async void ExecuteInterface()
        {

            try
            {
                //数据库连接
                ConString = JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
                SqlDb = (SqlServerHelper)DbHelperFactory.GetDbHelper(EFCore.Sharding.DatabaseType.SqlServer, ConString);

                //获取此接口的所有API信息
                this.interfaceOption = SqlDb.GetListBySql<Base_InterfaceOption>(@$"SELECT * FROM  Base_InterfaceOption(NOLOCK) WHERE Name='{InterfaceName}' ");
                Base_InterfaceOption queryOption = this.interfaceOption.Where(o => o.OperateType == "getFilterRows").First();//查询API配置
                Base_InterfaceOption insertOption = this.interfaceOption.Where(o => o.OperateType == "addRow").First();//新增API配置
                Base_InterfaceOption upDateOption = this.interfaceOption.Where(o => o.OperateType == "editRow").First();//更行API配置
                Base_InterfaceOption deleteOption = this.interfaceOption.Where(o => o.OperateType == "deleteRow").First();//删除API配置
                //需要获取期末数据的日期
                //string dateTo = DateTime.Now.AddMonths(-1).ToString("yyyy-MM");
                string dateTo = DateTime.Now.AddMonths(-queryOption.ExcInterval).ToString("yyyy-MM");

                //获取One_Link亚马逊MSKU信息
                AMZ_MSKUInterfaceBusiness aMZ_MSKU = new AMZ_MSKUInterfaceBusiness();
                aMZ_MSKU.Filters.Clear();
                aMZ_MSKU.Filters.Add(
               //13:>,14>=15:<,16<=
               new OL_FilterDTO() { ControlId = "", DataType = 2, SpliceType = 1, FilterType = 14, Value = "" }//只查询亚马逊的MSKU
           );
                List<AMZ_MSKU> MSKUList = await aMZ_MSKU.QueryAsync<AMZ_MSKU>(aMZ_MSKU.queryOption, aMZ_MSKU.tableField, aMZ_MSKU.SqlDb);
                MSKUList.ForEach(x =>
                {
                    //SKU
                    List<AssTable> table = x.sku_lock.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) x.sku_lock_T = table[0];
                    x.sku_lock = x.sku_lock_T.IsNullOrEmpty() ? "" : x.sku_lock_T.sid;
                    table = x.shop.ToObject<List<AssTable>>();
                    if (!table.IsNullOrEmpty() && table.Count > 0) x.shop_T = table[0];
                    x.shop = x.shop_T.IsNullOrEmpty() ? "" : x.shop_T.sid;
                });

                //获取上月的进销存数据
                Filters.Clear();
                Filters.Add(
                //13:>,14>=15:<,16<=
                new OL_FilterDTO() { ControlId = "68ea1f933c333678d696d871", DataType = 15, SpliceType = 1, FilterType = 14, Value = dateTo }//FDATE字段过滤日开
            );
                //    Filters.Add(
                //    //13:>,14>=15:<,16<=
                //    new OL_FilterDTO() { ControlId = "68ea1f933c333678d696d871", DataType = 15, SpliceType = 1, FilterType = 15, Value = DateTime.Now.ToString("yyyy-MM") }//FDATE字段过滤日开
                //);

                List<OL_Rep_InSalesStock> lastMonthData = await QueryAsync<OL_Rep_InSalesStock>(queryOption, tableField, SqlDb);
                List<OL_Rep_InSalesStock> sourceData = new List<OL_Rep_InSalesStock>();
                //将列表文本数据转换成列表对象
                if (lastMonthData.Count > 0 && !lastMonthData.IsNullOrEmpty())
                {
                    foreach (var item1 in lastMonthData)
                    {
                        //SKU
                        List<AssTable> table = item1.SKU.ToObject<List<AssTable>>();
                        if (!table.IsNullOrEmpty() && table.Count > 0)
                        {
                            item1.SKU_S = table[0].name;
                            item1.SKU = table[0].sid;
                        }
                    }
                }





                List<OL_Rep_InSalesStock> thisMonthData = new List<OL_Rep_InSalesStock>();
                //生成源数据,用上个月的期末数据生成本月的期初数据
                if (!lastMonthData.IsNullOrEmpty() && lastMonthData.Count > 0)
                {
                    foreach (var item1 in lastMonthData)
                    {
                        //如果日期月份等于本月，不再新增
                        if (item1.Date.ToDateTime().ToString("yyyy-MM") != DateTime.Now.ToString("yyyy-MM"))
                        {
                            OL_Rep_InSalesStock temp = new OL_Rep_InSalesStock();
                            temp.Date = item1.Date.ToDateTime().AddMonths(1).ToString("yyyy-MM");
                            temp.SKU = item1.SKU;
                            temp.SKU_S = item1.SKU_S;
                            temp.InitQty = item1.EndQty;
                            temp.InitUnitPrice = item1.EndUnitPrice;
                            temp.InitQty_Pur = item1.EndQty_Pur;
                            temp.InitUnitPrice_Pur = item1.EndUnitPrice_Pur;
                            temp.ID = temp.Date + temp.SKU;
                            thisMonthData.Add(temp);
                        }
                    }
                }
                //设置采购入库价格，本次入库总数
                thisMonthData = await SetPurPrieAndQty(thisMonthData);
                //设置货件收货/发货数量
                thisMonthData = await SetFBAShipment(thisMonthData, MSKUList);
                //计算本期销售发货数量

                //计算本期销售发货数量

                sourceData = thisMonthData;

                //获取目标数据
                dateTo = dateTo.ToDateTime().AddMonths(1).ToString("yyyy-MM");//需要获取更新数据的月份,比期末数据的月份大一个月
                Filters.Clear();
                Filters.Add(
                //13:>,14>=15:<,16<=
                new OL_FilterDTO() { ControlId = "68ea1f933c333678d696d871", DataType = 15, SpliceType = 1, FilterType = 14, Value = dateTo }//FDATE字段过滤日开
            );
                List<OL_Rep_InSalesStock> targetData = await QueryAsync<OL_Rep_InSalesStock>(queryOption, tableField, SqlDb);

                if (this.State > 0 && sourceData.Count > 0)
                {
                    //更新
                    #region
                    List<OL_Rep_InSalesStock> updataItem = sourceData.Intersect(targetData, new ComparerFromKeys<OL_Rep_InSalesStock>(Keys, "RowId")).ToList();//获取交集(更新项)
                    List<OL_RowDTO> updataData = ConvertOL_Row(updataItem, tableField);//需要更新的数据集合
                    ExecuteNoQuery<OL_RowDTO>(updataData, upDateOption, SqlDb);//执行更新
                    #endregion

                    //新增
                    #region
                    List<OL_Rep_InSalesStock> insertItem = sourceData.Except(targetData, new ComparerFromKey<OL_Rep_InSalesStock>(indexFields.First(), "RowId")).ToList();//获取源数据对目标数据的差集(新增项)
                    List<OL_RowDTO> insertData = ConvertOL_Row(insertItem, tableField);//需要新增的数据集合
                    ExecuteNoQuery<OL_RowDTO>(insertData, insertOption, SqlDb);//执行插入行
                    #endregion

                    //删除
                    #region
                    List<OL_Rep_InSalesStock> deleteItem = targetData.Except(sourceData, new ComparerFromKey<OL_Rep_InSalesStock>(indexFields.First(), "RowId")).ToList();//获取目标数对据源数据的差集(删除项)
                    List<OL_RowDTO> deleteData = ConvertOL_Row(deleteItem, tableField);//需要删除的数据集合
                    ExecuteNoQuery<OL_RowDTO>(deleteData, deleteOption, SqlDb);//执行删除行
                    #endregion
                }
            }


            catch (Exception ex)
            {
                AddLog(SqlDb, ex.Message, InterfaceName, "erro", "");
            }
        }




        /// <summary>
        /// 将源数据根据字典映射关系转换成OL_RowDTO
        /// </summary>
        /// <typeparam name="T">源数据</typeparam>
        /// <param name="sourceData">源数据</param>
        /// <param name="map">Key为目标字段名称，Value为源字段名称</param>
        /// <returns></returns>
        public List<OL_RowDTO> ConvertOL_Row<T>(List<T> sourceData, Dictionary<string, string> map)
        {
            List<OL_RowDTO> list = new List<OL_RowDTO>();
            object rowId;
            foreach (var item in sourceData)
            {
                rowId = item.GetPropertyValue("RowId");
                //需要新增的数据
                OL_RowDTO row = new OL_RowDTO() { RowId = rowId.IsNullOrEmpty() ? null : rowId.ToString(), Controls = new List<Control>() };
                foreach (var c in map)
                {
                    //获取字段的键值做判断
                    var field = Keys.Where(x => x.TField == c.Key).ToList();
                    string isKey = "";
                    if (field.Count > 0) isKey = field.First().Type;
                    //判断对象的字段是否存在
                    if (item.ContainsProperty(c.Value) && isKey != "N")
                    {
                        //存在则添加数据
                        row.Controls.Add(new Control() { ControlId = c.Key, Value = item.GetPropertyValue(c.Value) });
                    }
                }
                list.Add(row);
            }
            return list;
        }


        /// <summary>
        /// 设置采购入库价格，本次入库总数
        /// </summary>
        /// <param name="thisMonthData">本月数据</param>
        /// <returns></returns>
        public async Task<List<OL_Rep_InSalesStock>> SetPurPrieAndQty(List<OL_Rep_InSalesStock> thisMonthData)
        {
            var monthGroup = thisMonthData.GroupBy(x => x.Date);
            //List<OL_Rep_InSalesStock> thisMonthData_set = new List<OL_Rep_InSalesStock>();
            foreach (var item1 in monthGroup)
            {
                string monthFrom = item1.Key + "-01";
                string monthTo = monthFrom.ToDateTime().GetLastDay().ToString("yyyy-MM-dd");

                OL_PurInStocksInterfaceBusiness purInStocks = new OL_PurInStocksInterfaceBusiness();
                //获取对应时间的采购入库
                purInStocks.Filters.Clear();
                purInStocks.Filters.Add(
                //13:>,14>=15:<,16<=
                new OL_FilterDTO() { ControlId = "66c53db50604789daa3dd3b7", DataType = 15, SpliceType = 1, FilterType = 14, Value = monthFrom }//FDATE字段过滤日开
            );
                purInStocks.Filters.Add(
               //13:>,14>=15:<,16<=
               new OL_FilterDTO() { ControlId = "66c53db50604789daa3dd3b7", DataType = 15, SpliceType = 1, FilterType = 16, Value = monthTo }//FDATE字段过滤日开
           );
                List<OL_PurInStocks> purList = await purInStocks.QueryAsync<OL_PurInStocks>(purInStocks.queryOption, purInStocks.tableField, purInStocks.SqlDb);
                if (!purList.IsNullOrEmpty() && purList.Count > 0  )
                {
                    foreach (var i in purList)
                    {
                        //SKU
                        List<AssTable> table = i.SKU.ToObject<List<AssTable>>();
                        if (!table.IsNullOrEmpty() && table.Count > 0)
                        {
                            i.SKU_S = table[0].name;
                            i.SKU = table[0].sid;
                        }
                    }
                }



                //将对应时间采购成本的字段值设置到当月的进销存表中
                foreach (var row in thisMonthData)
                {
                    var item = purList.Where(x => x.SKU_S == row.SKU_S && x.Date.ToDateTime().ToString("yyyy-MM") == item1.Key);
                    if (!item.IsNullOrEmpty())
                    {
                        //设置已纳入计算的采购项，用于后续新增没有计算过的采购项进行新增进销存数据
                        item.ForEach(x => x.Note = "Y");
                        //计算本期采购入库数量(总数)
                        row.PurQty = item.Sum(x => x.StoreQty.ToDouble()).ToString("F2");

                        //计算本期采购单价=(上期采购库存数量*上期单价+本期采购入库数量*本期单价)/(期采购库存数量+本期采购入库数量)
                        var priceList = item.Where(x => !x.Price.IsNullOrEmpty());//获取价格不为空的价格项
                        //期初库存(采购)
                        var initQty_Pur = !row.InitQty_Pur.IsNullOrEmpty() && row.InitQty_Pur.ToDouble() > 0 ? row.InitQty_Pur.ToDouble() : 0;
                        //期初单价(采购)
                        var initUnitPrice_Pur = !row.InitUnitPrice_Pur.IsNullOrEmpty() && row.InitUnitPrice_Pur.ToDouble() > 0 ? row.InitUnitPrice_Pur.ToDouble() : 0;
                        //存在价格不为空的才进行计算
                        if (!priceList.IsNullOrEmpty())
                        {
                            var sumQty = priceList.Sum(x => x.StoreQty.ToDouble());//计算价格不为空的总数量
                            var sumPrice = priceList.Sum(x => x.StoreQty.ToDouble() * x.Price.ToDouble());//计算本次入库的总价
                            row.EndUnitPrice_Pur = ((initUnitPrice_Pur * initQty_Pur + sumPrice) / (initQty_Pur + sumQty)).ToString("F2");

                        }
                        else//没有单价则直接取期初的单价作为期末的单价
                        {
                            row.EndUnitPrice_Pur = row.InitUnitPrice_Pur;
                        }
                    }
                }


                var noCountItem = purList.Where(x => x.Note.IsNullOrEmpty());
                if (!noCountItem.IsNullOrEmpty())
                {
                    foreach (var item3 in noCountItem.GroupBy(x => x.SKU))
                    {
                        OL_Rep_InSalesStock temp = new OL_Rep_InSalesStock();
                        temp.SKU = item3.First().SKU;
                        temp.SKU_S = item3.First().SKU_S;
                        temp.Date = item3.First().Date.ToDateTime().ToString("yyyy-MM");
                        var qty = item3.Sum(x => x.StoreQty.ToDouble());
                        //计算总数量
                        temp.PurQty = qty.ToString();
                        var priceList = item3.Where(x => !x.Price.IsNullOrEmpty());//获取价格不为空的价格项
                        //存在价格不为空的才进行计算
                        if (!priceList.IsNullOrEmpty())
                        {
                            var sumQty = priceList.Sum(x => x.StoreQty.ToDouble());//计算价格不为空的总数量
                            var sumPrice = priceList.Sum(x => x.StoreQty.ToDouble() * x.Price.ToDouble());//计算本次入库的总价
                            temp.EndUnitPrice_Pur = (sumPrice / sumQty).ToString("F2");

                        }
                        else
                        {
                            temp.EndUnitPrice_Pur = "0";
                        }

                        thisMonthData.Add(temp);
                    }
                }

            }
            return thisMonthData;
        }


        public async Task<List<OL_Rep_InSalesStock>> SetFBAShipment(List<OL_Rep_InSalesStock> thisMonthData, List<AMZ_MSKU> MSKUList)
        {
            var monthGroup = thisMonthData.GroupBy(x => x.Date);
            foreach (var item1 in monthGroup)
            {
                string monthFrom = item1.Key + "-01";
                string monthTo = monthFrom.ToDateTime().GetLastDay().ToString("yyyy-MM-dd");

                this.SqlDb._conStrings[0]= JsonSettingHelper.GetSetting("Database:BaseDb:ConnectionString");
                var shipList = SqlDb.GetListBySql<FBAShipment>(@$"SELECT * FROM  SD_LX_FBAShipment(NOLOCK) WHERE ");

                //将对应时间采购成本的字段值设置到当月的进销存表中
              

            }
            return thisMonthData;
        }


        /// <summary>
        /// 任务
        /// </summary>
        public void Job()
        {
            ExecuteInterface();
        }
    }

}
